insert overwrite table jms_dws.dws_customer_day_cnt_dt partition(dt)
select                              
      dt                    as input_date
     ,customer_name
     ,customer_code
     ,attribute_id
     ,case when sum(ticket_quantity)>=5000 then 'A' 
           when sum(ticket_quantity)>=1000 then 'B' 
           when sum(ticket_quantity)>=500  then 'C'
           when sum(ticket_quantity)>=300  then 'D' 
        else 'E' end as customer_grade
     ,create_time
     ,sum(ticket_quantity)  as ticket_quantity
     ,franchisee_code
     ,franchisee_name
     ,agent_code
     ,agent_name
     ,provider_id
     ,city_id
     ,area_id
     ,provider_desc
     ,city_desc
     ,area_desc
     ,pick_network_code
     ,pick_network_name
     ,dt
from jms_dws.dws_customer_hour_cnt_dt
  where dt = '{{ execution_date | cst_ds }}'   
group by 
      customer_name
     ,customer_code
     ,attribute_id
     ,create_time
     ,franchisee_code
     ,franchisee_name
     ,agent_code
     ,agent_name
     ,provider_id
     ,city_id
     ,area_id
     ,provider_desc
     ,city_desc
     ,area_desc
     ,pick_network_code
     ,pick_network_name
     ,dt
  distribute by 1  ; 